﻿Imports System.Data.Linq
Public Class CChungTuController
    Dim objDataContext As QLyNganSachDataContext
    Public Function SelectAllRecord() As IQueryable(Of tblChungTu)
        Try
            Return (From ct In objDataContext.tblChungTus Order By ct.NgayChungTu, ct.SoChungTu Ascending Select ct)
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function SelectAllByDieuKien(ByVal idDanhMuc As Integer, ByVal iddonVi As Integer, ByVal idMaLoaiNganSach As Integer, ByVal thangQuyetToan As Integer, ByVal namQuyetToan As Integer, ByVal tuNgay As Date, ByVal denNgay As Date, ByVal soCT As String, ByVal dienGiai As String, ByVal soTienTu As Decimal, ByVal soTienDen As Decimal) As IQueryable(Of tblChungTu)
        Dim kq As IQueryable(Of tblChungTu)
        Try
            kq = SelectAllRecord()
            If (idDanhMuc > 0) Then
                kq = (From ct In kq
                          Where ct.tblBangKeChungTu.ID_DanhMuc = idDanhMuc
                          Select ct)
            End If
            If (iddonVi > 0) Then
                kq = (From ct In kq
                          Where ct.tblBangKeChungTu.Id_DonVi = iddonVi
                          Select ct)
            End If
            If (idMaLoaiNganSach > 0) Then
                kq = (From ct In kq
                          Where ct.tblBangKeChungTu.ID_MaLoaiNganSach = idMaLoaiNganSach
                          Select ct)
            End If
            If (thangQuyetToan > 0) Then
                kq = (From ct In kq
                      Where ct.tblBangKeChungTu.ThangQuyetToan = thangQuyetToan
                      Select ct)
            End If
            If (namQuyetToan > 0) Then
                kq = (From ct In kq
                      Where ct.tblBangKeChungTu.NamQuyetToan = namQuyetToan
                      Select ct)
            End If
            If (soCT <> "") Then
                kq = (From ct In kq
                      Where ct.SoChungTu.ToLower.Contains(soCT.ToLower)
                      Select ct)
            End If
            If (dienGiai <> "") Then
                kq = (From ct In kq
                      Where ct.NoiDung.ToLower.Contains(dienGiai.ToLower)
                      Select ct)
            End If
            If (tuNgay <> Date.MinValue) Then
                kq = (From ct In kq
                      Where ct.NgayChungTu >= tuNgay
                      Select ct)
            End If
            If (denNgay <> Date.MinValue) Then
                kq = (From ct In kq
                      Where ct.NgayChungTu <= denNgay
                      Select ct)
            End If
            If (soTienTu <> Decimal.MinValue) Then
                kq = (From ct In kq
                      Where ct.SoTien >= soTienTu
                      Select ct)
            End If
            If (soTienDen <> Decimal.MinValue) Then
                kq = (From ct In kq
                      Where ct.SoTien <= soTienDen
                      Select ct)
            End If
        Catch ex As Exception
            kq = Nothing
        End Try
        Return kq
    End Function
    Public Function SelectAllByDieuKien_2(ByVal idDanhMuc As Integer, ByVal iddonVi As Integer, ByVal idMaLoaiNganSach As Integer, ByVal thangQuyetToan As Integer, ByVal namQuyetToan As Integer, ByVal tuNgay As Date, ByVal denNgay As Date, ByVal soCT As String, ByVal dienGiai As String, ByVal soTienTu As Decimal, ByVal soTienDen As Decimal) As IQueryable(Of CChungTuInfo)
        Dim kq As IQueryable(Of tblChungTu)
        Try
            kq = SelectAllRecord()
            If (idDanhMuc > 0) Then
                kq = (From ct In kq
                          Where ct.tblBangKeChungTu.ID_DanhMuc = idDanhMuc
                          Select ct)
            End If
            If (iddonVi > 0) Then
                kq = (From ct In kq
                          Where ct.tblBangKeChungTu.Id_DonVi = iddonVi
                          Select ct)
            End If
            If (idMaLoaiNganSach > 0) Then
                kq = (From ct In kq
                          Where ct.tblBangKeChungTu.ID_MaLoaiNganSach = idMaLoaiNganSach
                          Select ct)
            End If
            If (thangQuyetToan > 0) Then
                kq = (From ct In kq
                      Where ct.tblBangKeChungTu.ThangQuyetToan = thangQuyetToan
                      Select ct)
            End If
            If (namQuyetToan > 0) Then
                kq = (From ct In kq
                      Where ct.tblBangKeChungTu.NamQuyetToan = namQuyetToan
                      Select ct)
            End If
            If (soCT <> "") Then
                kq = (From ct In kq
                      Where ct.SoChungTu.ToLower.Contains(soCT.ToLower)
                      Select ct)
            End If
            If (dienGiai <> "") Then
                kq = (From ct In kq
                      Where ct.NoiDung.ToLower.Contains(dienGiai.ToLower)
                      Select ct)
            End If
            If (tuNgay <> Date.MinValue) Then
                kq = (From ct In kq
                      Where ct.NgayChungTu >= tuNgay
                      Select ct)
            End If
            If (denNgay <> Date.MinValue) Then
                kq = (From ct In kq
                      Where ct.NgayChungTu <= denNgay
                      Select ct)
            End If
            If (soTienTu <> Decimal.MinValue) Then
                kq = (From ct In kq
                      Where ct.SoTien >= soTienTu
                      Select ct)
            End If
            If (soTienDen <> Decimal.MinValue) Then
                kq = (From ct In kq
                      Where ct.SoTien <= soTienDen
                      Select ct)
            End If
            'Return kq.ToList().Select(Function(ct) New CChungTuInfo With {
            '                                .ID_ChungTu = ct.ID_ChungTu,
            '                                 .SoChungTu = ct.SoChungTu,
            '                                 .NgayChungTu = ct.NgayChungTu.Value.ToString("dd/MM/yyyy"),
            '                                 .NoiDung = ct.NoiDung,
            '                                 .NguoiLienQuan = ct.NguoiLienQuan,
            '                                 .DiaChi = ct.DiaChi,
            '                                 .SoTien = ct.SoTien}).ToList()
            Return (From ct In kq
                    Order By ct.ID_ChungTu
               Select New CChungTuInfo With {.ID_ChungTu = ct.ID_ChungTu,
                                             .SoChungTu = ct.SoChungTu,
                                             .NgayChungTu = ct.NgayChungTu.Value.Day & "/" & ct.NgayChungTu.Value.Month & "/" & ct.NgayChungTu.Value.Year,
                                             .NoiDung = ct.NoiDung,
                                             .NguoiLienQuan = ct.NguoiLienQuan,
                                             .DiaChi = ct.DiaChi,
                                             .SoTien = ct.SoTien}
                )
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    Public Function SelectRecordByID(ByVal _id As Integer) As tblChungTu
        Try
            Return (From r In objDataContext.tblChungTus
                    Where r.ID_ChungTu = _id
                    Select r).Single
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function InsertRecord(ByVal LocalTable As tblChungTu) As Integer
        Dim id As Integer = -1
        Try
            objDataContext.tblChungTus.InsertOnSubmit(LocalTable)
            objDataContext.SubmitChanges()
            id = LocalTable.ID_ChungTu
        Catch ex As Exception
            Throw ex
        End Try
        Return id
    End Function
    Public Sub UpdateRecord(ByVal LocalTable As tblChungTu)
        Try
            objDataContext.tblChungTus.Attach(LocalTable)
            objDataContext.Refresh(RefreshMode.KeepCurrentValues, LocalTable)
            objDataContext.SubmitChanges(ConflictMode.ContinueOnConflict)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Sub UpdateRecordWithoutAttach(ByVal LocalTable As tblChungTu)
        Try
            objDataContext.Refresh(RefreshMode.KeepCurrentValues, LocalTable)
            objDataContext.SubmitChanges(ConflictMode.ContinueOnConflict)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub DeleteRecord(ByVal _id As Integer)
        Try
            objDataContext.tblChungTus.DeleteOnSubmit( _
              (From r In objDataContext.tblChungTus _
               Where r.ID_ChungTu = _id).Single)
            objDataContext.SubmitChanges()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Sub DeleteAllRecordInBangKe(ByVal _idBangKe As Integer)
        Try
            objDataContext.tblChungTus.DeleteAllOnSubmit( _
              (From r In objDataContext.tblChungTus _
               Where r.ID_BangKeChungTu = _idBangKe))
            objDataContext.SubmitChanges()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
  Public Function CheckIfExists(ByVal _id As String, ByVal _soCT As String, ByVal _ngayCT As Date) As Boolean
        Return objDataContext.tblChungTus.Any(Function(d) d.SoChungTu = _soCT.ToLower And d.NgayChungTu.Value.Year = _ngayCT.Year And d.ID_ChungTu <> _id)
    End Function
    Public Sub New()
        objDataContext = New QLyNganSachDataContext
    End Sub
    Public Sub New(ByVal _obj As QLyNganSachDataContext)
        objDataContext = _obj
    End Sub
End Class
